#!/usr/bin/env python
# -*- coding:utf-8 -*-
import sqlite3
import os
import traceback
import json
#global var
#数据库文件绝对路径
DB_PATH = os.path.join(os.getcwd(), "db")
DB_FILE_PATH = DB_PATH + "\\mitmex.db"
###############################################################
####            创建表操作     START
###############################################################
def get_conn(path):
    conn = sqlite3.connect(path)
    if os.path.exists(path) and os.path.isfile(path):
        return conn
    else:
        conn = None
        return sqlite3.connect(':memory:')
def get_cursor(conn):
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()
def create_table(conn, sql):
    '''创建数据库表'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        cu.execute(sql)
        conn.commit()
        print('创建数据库表成功!')
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))
def close_all(conn, cu):
    '''关闭数据库游标对象和数据库连接对象'''
    try:
        if cu is not None:
            cu.close()
    finally:
        if conn is not None:
            conn.close()
def mitmex_get_cur(path):
    conn = get_conn(path)
    cu = get_cursor(conn)
    return conn,cu
def create_table_httpmultipart():
    print('创建数据库表...')
    create_table_sql = '''CREATE TABLE `httpmultipart` (
                          `id` integer PRIMARY KEY autoincrement,
                          `method` varchar(20) NOT NULL,
                          `url` varchar(200) DEFAULT NULL,
                          `headers` varchar(4) DEFAULT NULL,
                          `params` varchar(1000) DEFAULT NULL,
                          `create_time` varchar(100) DEFAULT NULL,
                          `system_name` varchar(100) DEFAULT NULL,
                          `is_request` integer NOT NULL,
                          `is_response` integer NOT NULL
                        )'''
    conn = get_conn(DB_FILE_PATH)
    create_table(conn, create_table_sql)
###############################################################
####            删表操作     START
###############################################################
def drop_table_mitmex(table):
    if table is not None and table != '':
        sql = 'DROP TABLE IF EXISTS ' + table
        conn,cu = mitmex_get_cur(DB_FILE_PATH)
        cu.execute(sql)
        conn.commit()
        print('drop[{}]sucess!'.format(table))
        close_all(conn, cu)
    else:
        print('table name!')
###############################################################
####            DDL操作     START
###############################################################
#插入数据 table:httpmultipart
def mitmex_insert_data(dataDict):
    try:
        sql = '''INSERT INTO httpmultipart(method,url,headers,params,create_time,system_name,is_request,is_response) values (?, ?,?, ?, ?,?,?,?)'''
        data = (dataDict['method'],dataDict['url'],dataDict['headers'],
                dataDict['params'],dataDict['create_time'],dataDict['system_name'],
                dataDict['is_request'],dataDict['is_response'])
        conn,cu = mitmex_get_cur(DB_FILE_PATH)
        cu.execute(sql,data)
        conn.commit()
        close_all(conn,cu)
    except Exception as e:
        print(traceback.print_exc())
        close_all(conn,cu)
def mitmex_del_data_all(table):
    try:
        sql = '''delete from '''+table
        conn,cu = mitmex_get_cur(DB_FILE_PATH)
        cu.execute(sql)
        conn.commit()
        close_all(conn,cu)
    except Exception as e:
        print(traceback.print_exc())
        close_all(conn,cu)
#查询全部数据
def mitmex_select_all(table):
    try:
        data_dict = {}
        result = []
        sql = '''select * from '''+table
        conn,cu = mitmex_get_cur(DB_FILE_PATH)
        cu.execute(sql)
        r = cu.fetchall()
        if table == "httpmultipart":
            if len(r)>0:
                for e in range(len(r)):
                    data_dict['id'] = r[e][0]
                    data_dict['method'] = r[e][1]
                    data_dict['url'] = r[e][2]
                    data_dict['headers'] = eval(r[e][3])
                    data_dict['params'] = r[e][4]
                    data_dict['create_time']  = r[e][5]
                    data_dict['system_name'] = r[e][6]
                    data_dict['is_request'] = r[e][7]
                    data_dict['is_response'] = r[e][8]
                    result.append(data_dict)
                    data_dict = {}         
        close_all(conn,cu)
        return result
    except Exception as e:
        print(traceback.print_exc())
        close_all(conn,cu)
#获得最近的头信息
def mitmex_get_headers_by_system_name(table,systemName):
    rows = mitmex_select_max_data(table,"system_name",systemName,"headers")
    result = {}
    if table == 'httpmultipart':
        if len(rows)>0:
            for r in rows:
                result = eval(r[0])
    return result
#获得最近的参数值
def mitm_get_params_by_system_name(table,sysName):
    rows = mitmex_select_max_data(table,"system_name",systemName,"params")
    result = {}
    if table == 'httpmultipart':
        if len(rows)>0:
            for r in rows:
                result = r[0]
    return result  
#最大值
def mitmex_select_max_data(table,field,value,maxField):
    sql = '''select '''+maxField+''' from '''+table+''' where '''+field+''' = ? order by id desc limit 1'''
    data = (value,)
    conn,cu = mitmex_get_cur(DB_FILE_PATH)
    cu.execute(sql,data)
    r = cu.fetchall()
    close_all(conn,cu)
    return r
def main():
    #create_table_httpmultipart()
    #result = mitmex_select_all("httpmultipart")
    #for r in result:
        #print(r)
    #sysName = "192.168.16.134"
    #rows = mitmex_select_max_data("httpmultipart","system_name",sysName,"params")
    pass
if __name__ == '__main__':
    main()
